## 10.4. MysqlPerl

Monty Widenius, the author of MySQL, also wrote the Perl interface to MySQL, Mysql.pm. This was based on the mSQL module, Msql.pm. Thus, the interfaces of the two modules are almost identical. In fact, we recently converted an entire site from mSQL to MySQL by running "perl -e 's/^Msql/Mysql/" *.cgi" in every directory containing a CGI. This covers 95% of the work involved. Of course, this does not give you any of the advantages of MySQL, but it is a quick and easy way to start down the road to MySQL. Mysql.pm is maintained as part of msql-mysql-modules by Jochen Wiedmann.

> #### NOTE

> One of the largest differences between MySQL and mSQL is the way they handle sequences. In mSQL, a sequence is defined on a table with a command like `CREATE SEQUENCE on` tablename. The sequence value is then read as if it were a normal table value with the command `SELECT _seq from` tablename. MySQL adds the flag `AUTO_INCREMENT` to the primary key. Whenever a null value is inserted into this field, it is automatically incremented. Both MySQL and mSQL allow only one sequence per table. For a full discussion on sequences in MySQL and mSQL, see Chapter 6, "SQL According to MySQL and mSQL".

As an example of some of the features of Mysql.pm, let's go back to the tests example. Now that we have subject.cgi taken care of, the next step is the table of student information. The structure of the students table is as follows:

>     CREATE TABLE student (
>       id INT NOT NULL auto_increment,
>       first VARCHAR(50),
>       middle VARCHAR(50),
>       last VARCHAR(50),
>       ext VARCHAR(50),
>       subjects VARCHAR(100),
>       age INT,
>       sex INT,
>       address BLOB,
>       city VARCHAR(50),
>       state VARCHAR(5),
>       zip VARCHAR(10),
>       phone VARCHAR(10),
>       PRIMARY KEY (id)
>     )

All of the information used by the subject.cgi program is in this table, as well as other information pertaining to the student. The program that handles this table, student.cgi must perform all of the functions that subject.cgi did for the `subject` table.

> #### NOTE

> It is not possible to access a mSQL database with the Mysql.pm module, or MySQL with Msql.pm. The student.cgi program expects to find a MySQL version of the subjects table. Likewise, the subject.cgi program expects an mSQL version of the students table.

To illustrate the operation of Mysql.pm, we will examine in detail the portion of student.cgi that enables a user to change the information about a student. Just like the "add" action in the Msql.pm example was broken up into four separate functions, the "change" action here is separated into three individual functions.

The first function, change, prints out a form that allows the user to search for a student to change, as shown in the following:

>     sub change {
>        print header, start_html('title'=>'Student Change Search',
>           'BGCOLOR'=>'white');
>        &print_form('search2','Search for a Student to Change',1);
>        print <<END_OF_HTML;
>     <p>
>     <INPUT TYPE=HIDDEN NAME="subaction" VALUE="change2">
>     <INPUT TYPE=SUBMIT VALUE=" Search for Students ">
>      <INPUT TYPE=SUBMIT NAME="all" VALUE=" View all Students ">
>      <INPUT TYPE=RESET>
>     </form></body></html>
>     END_OF_HTML
>     }

The form used for searching for a student to "change" is so similar to the form used to searching for a student to "view" and the one to "add" a student that a single function, `print_form`, is used for all three purposes, as shown in the following:

>     sub print_form {
>        my ($action,$message,$any) = @_;

>        print <<END_OF_HTML;
>     <FORM METHOD=post ACTION="students.cgi">
>     <INPUT TYPE=HIDDEN NAME="action" VALUE="$action">
>     <H1>$message</h1>
>     END_OF_HTML
>        if ($any) {
>           print <<END_OF_HTML;
>     <p>Search for <SELECT NAME="bool">
>     <OPTION VALUE="or">any
>     <OPTION VALUE="and">all
>     </select> of your choices.
>     END_OF_HTML
>        }
>        print <<END_OF_HTML;
>     <p>
>     First: <INPUT NAME="first" SIZE=20>
>      Middle: <INPUT NAME="middle" SIZE=10>
>      Last: <INPUT NAME="last" SIZE=20>
>      Jr./III/etc.: <INPUT NAME="ext" SIZE=5>
>     <br>
>     Address: <INPUT NAME="address" SIZE=40><br>
>     City: <INPUT NAME="city" SIZE=20>
>      State: <INPUT NAME="state" SIZE=5>
>      ZIP: <INPUT NAME="zip" SIZE=10><br>
>     Phone: <INPUT NAME="phone" SIZE=15><br>
>     Age: <INPUT NAME="age" SIZE=5> Sex: <SELECT NAME="sex">
>     END_OF_HTML
>        if ($any) {
>           print <<END_OF_HTML;
>     <OPTION VALUE="">Doesn't Matter
>     END_OF_HTML
>        }
>        print <<END_OF_HTML;
>     <OPTION VALUE="1">Male
>     <OPTION VALUE="2">Female
>     </select><br>
>     <p>
>     Enrolled in:<br>
>     END_OF_HTML
>        &print_subjects("MULTIPLE SIZE=5");

>     }

By using three parameters, this function customizes a form template so that it can be used for several very different purposes. Notice that this helper function calls another helper function, `print_subjects`. This function queries the `subject` table as seen in the Msql.pm example and prints out a list of all of the available subjects.

>     sub print_subjects {
>        my $modifier = "";
>        $modifier = shift if @_;
>        print qq%<SELECT NAME="subjects" $modifier>\n%;
>        my $out = $dbh->query("select * from subject order by name");
>        while(my(%keys)=$out->fetchhash) {
>           print qq%<OPTION VALUE="$keys{'id'}">$keys{'name'}\n%;
>        }
>        print "</select>\n";
>     }

The search parameters entered in this first form are then sent to the `search2` function, which actually performs the search. This is actually the function written to search for a student to view. Since its function is exactly what we need, we can piggy-back off of it as long as we tell it that we want to go to the next change function, `change2`, after the search. That is why we have the hidden variable `subaction=change2` in the form. It tells `search2`, as shown in the following, where to send the user next:

>     sub search2 {
>        my $out = $dbh->query(&make_search_query);
>        my $hits = $out->numrows;
>        my $subaction = "view";
>        $subaction = param('subaction') if param('subaction');
>        print header, start_html('title'=>'Student Search Result',
>           'BGCOLOR'=>'white');

>        if (not $hits) {
>           print <<END_OF_HTML;
>     <H1>No students found</h1>
>     <p>
>     No students matched your criteria.
>     END_OF_HTML
>        } else {
>           print <<END_OF_HTML;
>     <H1>$hits students found</h1>
>     <p>
>     <UL>
>     END_OF_HTML
>           while(my(%fields)=$out->fetchhash) {
>              print qq%<LI>
>     <A HREF="students.cgi?action=$subaction&id=$fields{'id'}">$fields{'first'}
>      $fields{'middle'} $fields{'last'}%;
>              print ", $fields{'ext'}" if $fields{'ext'};
>              print "\n</a>";
>           }
>        }
>        print <<END_OF_HTML;
>     </ul>
>     <p>
>     <A HREF="students.cgi?action=search">Search</a> again.
>     </body></html>
>     END_OF_HTML
>     }

With help from the `make_search_query` function, this function first searches for students that match the search term. It then displays a list of the matches from which the user can select. The ID number of the selected entry is then sent to the `change2` function, as shown in the following:

>     sub change2 {
>           my $out = $dbh->query("select * from student where id=$id");

>           my($did,$first,$middle,$last,$ext,$subjects,$age,$sex,$address,
>              $city,$state,$zip,$phone) = $out->fetchrow;

>           my @subjects = split(/:/,$subjects);
>           shift @subjects;
>           my $name = "$first $middle $last";
>           if ($ext) { $name .= ", $ext"; }

>           print header, start_html('title'=>"$name",'BGCOLOR'=>'white');
>           print <<END_OF_HTML;
>     <H1>$name</h1>
>     <p>
>     <FORM ACTION="students.cgi" METHOD=POST>
>     <INPUT TYPE=HIDDEN NAME="action" VALUE="change3">
>     <INPUT TYPE=HIDDEN NAME="id" VALUE="$id">
>     First: <INPUT NAME="first" VALUE="$first" SIZE=20>
>     Middle: <INPUT NAME="middle" VALUE="$middle" SIZE=10>
>     Last: <INPUT NAME="last" VALUE="$last" SIZE=20>
>     Jr./III/etc.: <INPUT NAME="ext" VALUE="$ext" SIZE=5>
>     <br>
>     Address: <INPUT NAME="address" VALUE="$address" SIZE=40><br>
>     City: <INPUT NAME="city" VALUE="$city" SIZE=20>
>     State: <INPUT NAME="state" VALUE="$state" SIZE=5>
>     ZIP: <INPUT NAME="zip" VALUE="$zip" SIZE=10><br>
>     Phone: <INPUT NAME="phone" VALUE="$phone" SIZE=15><br>
>     Age: <INPUT NAME="age" VALUE="$age" SIZE=5> Sex:
>     END_OF_HTML
>           my %sexes = ( '1' => 'Male',
>              '2' => 'Female'
>           );
>           print popup_menu('name'=>'sex',
>              'values'=>['1','2'],
>              'default'=>"$sex",
>              'labels'=>\%sexes);
>           print <<END_OF_HTML;
>     <p>
>     Enrolled in:<br>
>     END_OF_HTML
>           my @ids = ();
>           my %subjects = ();
>           my $out2 = $dbh->query("select id,name from subject order by name");
>           while(my($id,$subject)=$out2->fetchrow) {
>              push(@ids,$id);
>              $subjects{"$id"} = $subject;
>           }
>           print scrolling_list('name'=>'subjects',
>              'values'=>[@ids],
>              'default'=>[@subjects],
>              'size'=>5,
>              'multiple'=>'true',
>              'labels'=>\%subjects);
>           print <<END_OF_HTML;
>     <p>
>     <INPUT TYPE=SUBMIT VALUE=" Change Student ">
>      <INPUT TYPE=SUBMIT NAME="delete" VALUE=" Delete Student ">
>      <INPUT TYPE=RESET>
>     </form></body></html>
>     END_OF_HTML
>     }

The primary purpose of this function is to print out a form very similar to the one generated from `print_from`. However, the values of this form must have the values of the chosen student preinserted as default values. This way, the user can edit whichever fields of the student he or she wishes without changing the rest.

A couple of functions provided by the CGI.pm module come in very handy when printing form with default values. Most importantly, the function `CGI::scrolling_list` prints out an HTML `<SELECT>` block with the parameters you provide. Among other parameters, the function takes the parameters `values`, `default`, and `labels` which are references to the values of each `<OPTION>` tag, the ones which should be preselected and the labels that user sees respectively.

The output of this function is a complete set of information, just as if it were coming from an add form. The difference is that the data is for a student which already exists in the database. The `change3` function accepts this data and updates the student, as shown in the following:

>     sub change3 {
>        if (param('delete')) { &delete2($id); }
>        else {
>           my $query = "update student set ";
>           my @query = ();
>           foreach ('first', 'middle', 'last', 'ext', 'address', 'city',
>              'state', 'zip', 'phone') {
>              if (param($_)) { push(@query,"$_ = ".
>                 $dbh->quote(param($_)));
>              }
>           }
>           push(@query,"age=".param('age')) if param('age');
>           push(@query,"sex=".param('sex')) if param('sex');

>           my $subjects = "':";
>           $subjects .= join(":",param('subjects'));
>           $subjects .= ":" unless $subjects eq "':";
>           $subjects .= "'";
>           push(@query,"subjects=$subjects");

>           $query .= join(", ",@query) . " where id=$id";
>           $dbh->query($query);

>           print header, start_html('title'=>'Student Changed',
>              'BGCOLOR'=>'white');
>           # Print success form
>           ...
>        }
>     }

Note that if the user chose the "Delete" button on the change page, this function automatically passes the ball to the delete function. This is one major advantage of integrating several functions into one program. If no user interaction is required, you can skip from function to function without sending redirect messages to the user.

The rest of this function is fairly straightforward. The information about the student is gathered into an `UPDATE` query, which is sent to the MySQL server. A success page is then sent to the user.

Copyright © 2001 O'Reilly & Associates. All rights reserved.